#include "databasemanager.h"
#include "fileUtil.hpp"
#include "dataManager.hpp"
#include "clientBackup.hpp"
#include <string>

extern DatabaseManager dbManager;

DatabaseManager::DatabaseManager(QObject *parent)
    : QObject(parent)
{
    buildConnection();
}


DatabaseManager::~DatabaseManager()
{
    if (db.isOpen())
        db.close();
    qDebug() << db << "db closed! " ;
}

bool DatabaseManager::buildConnection()
{
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(HOST); // 数据库服务器地址
    db.setPort(PORT);
    db.setDatabaseName(DATABASE); // 数据库名
    db.setUserName(USER); // 数据库用户名
    db.setPassword(PASSWD); // 数据库密码

    // 尝试打开数据库
    if (!db.open())
    {
        qDebug() << "数据库连接失败：" <<  db.lastError().text();
        return false;
    }
    qDebug() << DATABASE << " connect success!";
    return true;
}

//通过手机号判断该用户是否存在
bool DatabaseManager::isUserExistByPhone(const QString &phone)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery sqlQuery;
    sqlQuery.prepare("select user_id from users where phone_number = :phone");
    sqlQuery.bindValue(":phone", phone);
    if(sqlQuery.exec() && sqlQuery.next())
        return true;

    return false;
}

bool DatabaseManager::isUserExistByUsrName(const QString &UsrName)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery sqlQuery;
    sqlQuery.prepare("select user_id from users where user_name = :UsrName");
    sqlQuery.bindValue(":UsrName", UsrName);
    if(sqlQuery.exec() && sqlQuery.next())
        return true;

    return false;
}

// 登录时 账号和密码匹配
bool DatabaseManager::loginInputMatches(const QString &account, const QString &inputPasswd)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery sqlQuery;
    if(account.startsWith('1'))
    {
        sqlQuery.prepare("select password from users where phone_number = :phone");
        sqlQuery.bindValue(":phone", account);
    }
    else
    {
        sqlQuery.prepare("select password from users where user_name = :user_name");
        sqlQuery.bindValue(":user_name", account);
    }

    if(!sqlQuery.exec() || !sqlQuery.next())
    {
        qDebug()<< db.lastError().text();
        return false;
    }

    if(sqlQuery.value("password").toString() == inputPasswd)
        return true;

    return false;
}

bool DatabaseManager::usrExisted(const QString &usrName)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery sqlQuery;
    sqlQuery.prepare("select * from users where user_name = :usrName");
    sqlQuery.bindValue(":usrName", usrName);
    if(sqlQuery.exec() && sqlQuery.next())
    {
        return true;
    }
    else
        return false;
}

bool DatabaseManager::phoneExisted(const QString &phone)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery sqlQuery;
    sqlQuery.prepare("select * from users where phone_number = :phone");
    sqlQuery.bindValue(":phone", phone);
    if(sqlQuery.exec() && sqlQuery.next())
    {
        return true;
    }
    else
        return false;
}

bool DatabaseManager::addNewUser(const QString &usrName, const QString &phone, const QString &password)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }
    QSqlQuery query;
    query.prepare("INSERT INTO users (user_name, phone_number, password) VALUES (:username, :phone, :password)");
    query.bindValue(":username", usrName);
    query.bindValue(":phone", phone);
    query.bindValue(":password", password);
    // 开始事务
    if (!db.transaction())
    {
        qDebug() << "开始事务失败:" << db.lastError().text();
        return false;
    }

    if (!query.exec())
    {
        qDebug() << "Query failed: " << query.lastError().text();
        return false;
    }
    // 如果所有操作成功，提交事务
    if (!db.commit())
    {
        qDebug() << "提交事务失败:" << db.lastError().text();
        return false;
    }
    return true;
}

bool DatabaseManager::updateLoginTime(const QString& account)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    // 创建一个QDateTime对象
    QDateTime dateTime = QDateTime::currentDateTime();
    QString formattedDateTime = dateTime.toString("yyyy-MM-dd HH:mm:ss");

    QSqlQuery query;
    if(account.startsWith('1'))
    {
        query.prepare("update users set last_login = :last_login_time where phone_number = :account");
        query.bindValue(":last_login_time", formattedDateTime);
        query.bindValue(":account", account);
    }
    else
    {
        query.prepare("update users set last_login = :last_login_time where user_name = :account");
        query.bindValue(":last_login_time", formattedDateTime);
        query.bindValue(":account", account);
    }
    // 开始事务
    if (!db.transaction())
    {
        qDebug() << "开始事务失败:" << db.lastError().text();
        return false;
    }

    // 执行查询
    if (!query.exec())
    {
        qDebug() << "Query failed: " << query.lastError().text();
        return false;
    }
    // 如果所有操作成功，提交事务
    if (!db.commit())
    {
        qDebug() << "提交事务失败:" << db.lastError().text();
        return false;
    }
    return true;
}

int DatabaseManager::getIdByAccount(const QString &account)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }
    QSqlQuery query;
    if(account.startsWith('1'))
    {
        query.prepare("select user_id from users where phone_number = :phone");
        query.bindValue(":phone", account);
    }
    else
    {
        query.prepare("select user_id from users where user_name = :name");
        query.bindValue(":name", account);
    }

    if (!query.exec())
    {
        qDebug() << "Query failed: " << query.lastError().text();
        return -1;
    }

    if(query.next())
    {
        return query.value("user_id").toInt();
    }
    return -1;
}

// 从数据库读取path+etag 存到fileInfoMap
bool DatabaseManager::readFileAInfo(int usrID, std::unordered_map<std::string, std::string> &fileInfoMap)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    // 根据用户ID从upload_files表中读取数据插入到内存的map
    QSqlQuery query;
    query.prepare("select file_path, file_etag from upload_files where user_id = :usrID");
    query.bindValue(":user_id", usrID);
    if (!query.exec())
    {
        qDebug() << "Query failed: " << query.lastError().text();
        return false;
    }

    // 读取数据前确保ClientUploadedFiles表中有数据 没数据不读取
    std::string filePath;
    std::string fileEtag;
    while(query.next())
    {
        filePath = query.value("file_path").toString().toStdString();
        fileEtag = query.value("file_etag").toString().toStdString();
        //qDebug() << QString::fromStdString(filePath) << QString::fromStdString(fileEtag);

        cloudBackup::FileUtil fileUtil(filePath);
        // 添加到map前 确保path文件存在 以防数据库有信息 但本地无此文件
        if (!fileUtil.Exists())
        {
            removeRecord(usrID, filePath);
            continue;
        }
        fileInfoMap[filePath] = fileEtag;
    }

    return true;
}

// 文件被修改 更新数据库记录file_etag file_status
bool DatabaseManager::modifyRecord(const std::string &key, const std::string &val, int usrID)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery query2;
    query2.prepare(R"(
                   UPDATE backup_records SET file_status = 'modified', file_etag = :fileEtag
                   where file_etag = (
                   SELECT file_etag FROM upload_files
                   WHERE user_id = :usrID AND file_path = :filePath)
                   )");

    query2.bindValue(":fileEtag", QString::fromStdString(val));
    query2.bindValue(":usrID", usrID);
    query2.bindValue(":filePath", QString::fromStdString(key));
    // 开始事务
    if (!db.transaction())
    {
        qDebug() << "开始事务失败:" << db.lastError().text();
        return false;
    }

    if (!query2.exec())
    {
        qDebug() << "Query failed: " << query2.lastError().text();
        return false;
    }
    // 如果所有操作成功，提交事务
    if (!db.commit())
    {
        qDebug() << "提交事务失败:" << db.lastError().text();
        return false;
    }
    QSqlQuery query;
    query.prepare("update upload_files set file_etag = :fileEtag where user_id = :usrID and file_path = :filePath");
    query.bindValue(":fileEtag", QString::fromStdString(val));
    query.bindValue(":user_id", usrID);
    query.bindValue(":file_path", QString::fromStdString(key));
    // 开始事务
    if (!db.transaction())
    {
        qDebug() << "开始事务失败:" << db.lastError().text();
        return false;
    }

    if (!query.exec())
    {
        qDebug() << "Query failed: " << query.lastError().text();
        return false;
    }
    // 如果所有操作成功，提交事务
    if (!db.commit())
    {
        qDebug() << "提交事务失败:" << db.lastError().text();
        return false;
    }
    return true;
}

// 文件被删除 更新备份记录表 根据usrID和filePath删除数据库记录
bool DatabaseManager::removeRecord(int usrID, const std::string &key)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery query2;
    query2.prepare(R"(
                   UPDATE backup_records SET file_status = 'deleted'
                   WHERE file_etag = (
                   SELECT file_etag FROM upload_files
                   WHERE user_id = :usrID AND file_path = :filePath
                   )
                   )");

    query2.bindValue(":usrID", usrID);
    query2.bindValue(":filePath", QString::fromStdString(key));
    // 开始事务
    if (!db.transaction())
    {
        qDebug() << "开始事务失败:" << db.lastError().text();
        return false;
    }

    if (!query2.exec())
    {
        qDebug() << "Query failed: " << query2.lastError().text();
        return false;
    }
    // 如果所有操作成功，提交事务
    if (!db.commit())
    {
        qDebug() << "提交事务失败:" << db.lastError().text();
        return false;
    }
    QSqlQuery query;
    query.prepare("delete from upload_files where user_id = :usrID and file_path = :filePath");
    query.bindValue(":usrID", usrID);
    query.bindValue(":filePath", QString::fromStdString(key));
    // 开始事务
    if (!db.transaction())
    {
        qDebug() << "开始事务失败:" << db.lastError().text();
        return false;
    }

    if (!query.exec())
    {
        qDebug() << "Query failed: " << query.lastError().text();
        return false;
    }

    // 如果所有操作成功，提交事务
    if (!db.commit())
    {
        qDebug() << "提交事务失败:" << db.lastError().text();
        return false;
    }
    return true;
}

// 新增文件 插入数据库记录
bool DatabaseManager::insertRecord(int usrID, const std::string &key, const std::string &val)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery query;
    query.prepare("INSERT INTO upload_files(user_id, file_path, file_etag) VALUES (:userID, :filePath, :fileEtag)");
    query.bindValue(":userID", usrID);
    query.bindValue(":filePath", QString::fromStdString(key));
    query.bindValue(":fileEtag", QString::fromStdString(val));
    // 开始事务
    if (!db.transaction())
    {
        qDebug() << "开始事务失败:" << db.lastError().text();
        return false;
    }

    if (!query.exec())
    {
        qDebug() << "插入到客户端上传表 失败： " << query.lastError().text();
        return false;
    }
    // 如果所有操作成功，提交事务
    if (!db.commit())
    {
        qDebug() << "提交事务失败:" << db.lastError().text();
        return false;
    }
    QSqlQuery query2;
    query2.prepare("INSERT INTO backup_records(user_id, file_etag) VALUES (:userID, :fileEtag)");
    query2.bindValue(":userID", usrID);
    query2.bindValue(":fileEtag", QString::fromStdString(val));
    // 开始事务
    if (!db.transaction())
    {
        qDebug() << "开始事务失败:" << db.lastError().text();
        return false;
    }

    if (!query2.exec())
    {
        qDebug() << "插入到备份记录表 失败： " << query2.lastError().text();
        return false;
    }
    // 如果所有操作成功，提交事务
    if (!db.commit())
    {
        qDebug() << "提交事务失败:" << db.lastError().text();
        return false;
    }
    return true;
}

bool DatabaseManager::getPasswdByUsrID(int usrID, QString &passwd)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery query;
    query.prepare("select password from users where user_id = :usrID");
    query.bindValue(":usrID", usrID);
    if (!query.exec())
    {
        qDebug() << "Query failed: " << query.lastError().text();
        return false;
    }

    if(!query.next())
    {
        return false;
    }

    passwd = query.value("password").toString();
    return true;
}

bool DatabaseManager::getUsrNameByUsrID(int usrID, QString &usrName)
{
    if (!db.isOpen())
    {
        qDebug() <<"db no connect"<< db.lastError().text();
        return false;
    }

    QSqlQuery query;
    query.prepare("select user_name from users where user_id = :usrID");
    query.bindValue(":usrID", usrID);
    if (!query.exec())
    {
        qDebug() << "Query failed: " << query.lastError().text();
        return false;
    }

    if(!query.next())
    {
        return false;
    }

    usrName = query.value("user_name").toString();
    return true;
}








